package cn.thoughtworks.school.repositories;

import cn.thoughtworks.school.entities.Program;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface ProgramDiffRepository extends JpaRepository<Program, Long> {
    @Query(value = "select * from program where " +
        "     id in(select  max(id) from (\n" +
        "     select @ids as _ids,\n" +
        "    (select @ids \\:= group_concat(programId) from program where FIND_IN_SET(fromId,@ids)) as cids from program,\n" +
        "    (select @ids \\:=?1) b where @ids is not null\n" +
        "    )  ID,\n" +
        "    program DATA where FIND_IN_SET(DATA.programId,ID._ids) group by programId)", nativeQuery = true)
    List<Program> getProgramChildren(Long programId);

    @Query(value = "select * from program where operation != 2 and  id in (" +
        "        select  max(id) from  (\n" +
        "        select @programId as _id, (\n" +
        "        select  @programId \\:= fromId from program where programId =@programId limit 1 ) as _pid from program,\n" +
        "        (select  @programId \\:=?1) b ) ID,\n" +
        "       program DATA where ID._id = DATA.programId  group by DATA.programId)", nativeQuery = true)
    List<Program> getProgramParents(Long programId);

    Program findFirst1ByProgramIdOrderByIdDesc(Long id);

    List<Program> findAllByProgramIdOrderByIdDesc(Long programId);
}
